#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
营销CRM测试数据生成器
生成客户、联系人、跟进记录、订单等测试数据
"""

import random
import datetime
from typing import List, Dict

# 配置数据
CUSTOMER_COUNT = 50  # 生成客户数量

# 行业列表
INDUSTRIES = [
    "科技互联网", "金融保险", "教育培训", "医疗健康", "房地产建筑",
    "制造业", "电商零售", "文化传媒", "物流运输", "咨询服务",
    "餐饮酒店", "旅游度假", "法律服务", "会计审计", "政府机构"
]

# 企业规模
SCALES = [
    "1-10人", "11-50人", "51-200人", "201-500人", "501-1000人", "1000人以上"
]

# 客户来源
SOURCES = [
    "官网咨询", "电话营销", "朋友介绍", "展会获取", "网络推广",
    "老客户推荐", "线上广告", "陌生拜访", "行业合作", "社交媒体"
]

# 省市区数据
REGIONS = [
    ("北京市", "朝阳区", "三里屯"),
    ("北京市", "海淀区", "中关村"),
    ("上海市", "浦东新区", "陆家嘴"),
    ("上海市", "徐汇区", "徐家汇"),
    ("广州市", "天河区", "珠江新城"),
    ("深圳市", "南山区", "科技园"),
    ("深圳市", "福田区", "中心区"),
    ("杭州市", "西湖区", "文三路"),
    ("杭州市", "滨江区", "高新"),
    ("成都市", "高新区", "天府软件园"),
    ("武汉市", "洪山区", "光谷"),
    ("南京市", "建邺区", "河西"),
    ("苏州市", "工业园区", "金鸡湖"),
    ("西安市", "高新区", "科技路"),
    ("重庆市", "渝北区", "观音桥"),
]

# 客户类型
CUSTOMER_TYPES = {
    1: "企业客户",
    2: "个人客户",
    3: "政府客户"
}

# 客户状态
CUSTOMER_STATUS = {
    1: "潜在客户",
    2: "跟进中",
    3: "已合作",
    4: "公海客户",
    5: "已流失"
}

# 姓名库
FIRST_NAMES = ["张", "李", "王", "赵", "刘", "陈", "杨", "黄", "周", "吴", "徐", "孙", "朱", "马", "胡", "郭", "林", "何", "高", "梁"]
LAST_NAMES = ["伟", "芳", "娜", "秀英", "敏", "静", "丽", "强", "磊", "军", "洋", "勇", "艳", "杰", "娟", "涛", "明", "超", "秀兰", "霞"]

# 公司名称前缀
COMPANY_PREFIX = [
    "北京", "上海", "广州", "深圳", "杭州", "成都", "武汉", "南京", "苏州", "西安",
    "腾讯", "阿里巴巴", "百度", "华为", "小米", "美团", "滴滴", "京东", "网易", "新浪",
    "华润", "万科", "恒大", "碧桂园", "中海", "绿地", "保利", "融创", "龙湖", "招商"
]

COMPANY_SUFFIX = [
    "科技有限公司", "贸易有限公司", "管理咨询有限公司", "文化传播有限公司",
    "建筑设计有限公司", "投资有限公司", "实业有限公司", "网络科技有限公司",
    "教育科技有限公司", "医疗科技有限公司", "汽车服务有限公司", "餐饮管理有限公司",
    "房地产经纪有限公司", "人力资源有限公司", "广告传媒有限公司", "环保科技有限公司"
]

# 跟进方式
FOLLOWUP_TYPES = {
    1: "电话联系",
    2: "微信沟通",
    3: "邮件联系",
    4: "上门拜访",
    5: "会议沟通"
}

# 跟进结果
FOLLOWUP_RESULTS = {
    1: "非常感兴趣",
    2: "比较感兴趣",
    3: "一般",
    4: "不太感兴趣",
    5: "拒绝"
}

# 订单类型
ORDER_TYPES = {
    1: "软件定制",
    2: "系统维护",
    3: "咨询服务",
    4: "培训服务",
    5: "产品采购"
}

# 订单状态
ORDER_STATUS = {
    1: "待处理",
    2: "进行中",
    3: "已完成",
    4: "已取消",
    5: "已暂停"
}

# 付款状态
PAYMENT_STATUS = {
    1: "待付款",
    2: "部分付款",
    3: "已付款",
    4: "逾期未付"
}

def generate_random_name() -> str:
    """生成随机姓名"""
    return random.choice(FIRST_NAMES) + random.choice(LAST_NAMES)

def generate_company_name() -> str:
    """生成公司名称"""
    return random.choice(COMPANY_PREFIX) + random.choice(COMPANY_SUFFIX)

def generate_phone() -> str:
    """生成手机号"""
    prefixes = ['130', '131', '132', '133', '134', '135', '136', '137', '138', '139',
                '150', '151', '152', '153', '155', '156', '157', '158', '159',
                '180', '181', '182', '183', '184', '185', '186', '187', '188', '189']
    return random.choice(prefixes) + ''.join([str(random.randint(0, 9)) for _ in range(8)])

def generate_email(name: str) -> str:
    """生成邮箱"""
    domains = ['163.com', 'qq.com', '126.com', 'gmail.com', 'sina.com', 'sohu.com']
    # 转换为拼音或直接使用拼音首字母
    name_pinyin = name.lower()
    return f"{name_pinyin}{random.randint(10, 99)}@{random.choice(domains)}"

def generate_address(region: tuple) -> str:
    """生成地址"""
    streets = ["建国路", "中山路", "人民大道", "解放街", "和平路", "文化路",
               "科技大道", "商务中心", "CBD", "中心广场", "金融街", "创业园"]
    numbers = random.randint(1, 999)
    return f"{region[0]}{region[1]}{region[2]}{random.choice(streets)}{numbers}号"

def generate_datetime(start_days_ago: int = 365, end_days_ago: int = 0) -> datetime.datetime:
    """生成随机时间"""
    start = datetime.datetime.now() - datetime.timedelta(days=start_days_ago)
    end = datetime.datetime.now() - datetime.timedelta(days=end_days_ago)
    return start + datetime.timedelta(
        seconds=random.randint(0, int((end - start).total_seconds()))
    )

def generate_customers(count: int) -> List[Dict]:
    """生成客户数据"""
    customers = []

    # 确保有一些特殊状态的客户
    special_statuses = [1, 2, 3, 4, 5]  # 各种状态都包含
    for status in special_statuses:
        customer = {
            'id': len(customers) + 1,
            'name': generate_company_name(),
            'legal_person': generate_random_name(),
            'phone': generate_phone(),
            'industry': random.choice(INDUSTRIES),
            'scale': random.choice(SCALES),
            'source': random.randint(1, 10),
            'province': random.choice(REGIONS)[0],
            'city': random.choice(REGIONS)[1],
            'district': random.choice(REGIONS)[2],
            'address': generate_address(random.choice(REGIONS)),
            'type': random.choice([1, 2, 3]),  # 企业、个人、政府
            'status': status,  # 设置特定状态
            'cooperation_time': None,
            'user_id': random.randint(1, 10),  # 假设有10个销售
            'department_id': random.randint(1, 5),  # 假设有5个部门
            'create_time': generate_datetime(365, 30),
            'update_time': None
        }

        # 如果是已合作客户，设置合作时间
        if status == 3:
            customer['cooperation_time'] = int(generate_datetime(300, 0).timestamp())
            customer['update_time'] = generate_datetime(300, 0)
        else:
            customer['update_time'] = generate_datetime(365, 0)

        customers.append(customer)

    # 生成剩余客户
    for i in range(count - len(special_statuses)):
        status = random.choices(
            population=[1, 2, 3, 4, 5],
            weights=[30, 35, 20, 10, 5]
        )[0]

        customer = {
            'id': len(customers) + 1,
            'name': generate_company_name(),
            'legal_person': generate_random_name(),
            'phone': generate_phone(),
            'industry': random.choice(INDUSTRIES),
            'scale': random.choice(SCALES),
            'source': random.randint(1, 10),
            'province': random.choice(REGIONS)[0],
            'city': random.choice(REGIONS)[1],
            'district': random.choice(REGIONS)[2],
            'address': generate_address(random.choice(REGIONS)),
            'type': random.choice([1, 2, 3]),
            'status': status,
            'cooperation_time': None,
            'user_id': random.randint(1, 10),
            'department_id': random.randint(1, 5),
            'create_time': generate_datetime(365, 0),
            'update_time': None
        }

        if status == 3:
            customer['cooperation_time'] = int(generate_datetime(300, 0).timestamp())
            customer['update_time'] = generate_datetime(300, 0)
        else:
            customer['update_time'] = generate_datetime(365, 0)

        customers.append(customer)

    return customers

def generate_contacts(customers: List[Dict]) -> List[Dict]:
    """生成联系人数据"""
    contacts = []
    contact_id = 1

    for customer in customers:
        # 每个客户至少1个联系人，最多3个
        num_contacts = random.randint(1, 3)

        for i in range(num_contacts):
            contact = {
                'id': contact_id,
                'customer_id': customer['id'],
                'name': generate_random_name(),
                'position': random.choice(["总经理", "销售总监", "市场经理", "采购经理", "项目经理", "董事长秘书", "办公室主任"]),
                'phone': generate_phone(),
                'wechat': f"wx_{random.randint(100000, 999999)}",
                'email': generate_email(generate_random_name()),
                'is_primary': True if i == 0 else False,
                'status': 1,  # 正常
                'user_id': customer['user_id'],
                'create_time': customer['create_time'],
                'update_time': customer['update_time']
            }
            contacts.append(contact)
            contact_id += 1

    return contacts

def generate_followups(customers: List[Dict]) -> List[Dict]:
    """生成跟进记录数据"""
    followups = []
    followup_id = 1

    for customer in customers:
        # 不同状态的客户跟进记录数量不同
        if customer['status'] == 1:  # 潜在客户
            num_followups = random.randint(0, 2)
        elif customer['status'] == 2:  # 跟进中
            num_followups = random.randint(2, 8)
        elif customer['status'] == 3:  # 已合作
            num_followups = random.randint(3, 15)
        elif customer['status'] == 4:  # 公海客户
            num_followups = random.randint(0, 1)
        else:  # 已流失
            num_followups = random.randint(1, 3)

        for i in range(num_followups):
            followup_time = generate_datetime(365, 0)
            next_followup = followup_time + datetime.timedelta(days=random.randint(3, 30))

            followup = {
                'id': followup_id,
                'customer_id': customer['id'],
                'followup_type': random.randint(1, 5),
                'followup_time': followup_time.strftime('%Y-%m-%d %H:%M:%S'),
                'content': random.choice([
                    "客户对产品很感兴趣，已发送详细资料",
                    "约定了下次沟通时间，详细介绍产品功能",
                    "客户提出了一些问题，已详细解答",
                    "客户需要与团队讨论后回复",
                    "客户认为价格偏高，协商中",
                    "客户表示需要领导审批",
                    "客户要求试用产品，已安排",
                    "客户近期有采购计划",
                    "客户在其他平台已有类似产品",
                    "建立了良好沟通关系"
                ]),
                'followup_result': random.randint(1, 5),
                'next_followup_time': next_followup.strftime('%Y-%m-%d %H:%M:%S'),
                'user_id': customer['user_id'],
                'create_time': followup_time,
                'update_time': followup_time
            }
            followups.append(followup)
            followup_id += 1

    return followups

def generate_orders(customers: List[Dict]) -> List[Dict]:
    """生成订单数据"""
    orders = []
    order_id = 1

    for customer in customers:
        if customer['status'] in [2, 3]:  # 跟进中和已合作的客户可能有订单
            # 已合作客户通常有1-5个订单，潜在客户可能有关注的订单
            if customer['status'] == 3:
                num_orders = random.randint(1, 5)
            else:
                num_orders = random.choices(
                    population=[0, 1, 2],
                    weights=[40, 50, 10]
                )[0]

            for i in range(num_orders):
                order_time = generate_datetime(300, 0)
                order = {
                    'id': order_id,
                    'customer_id': customer['id'],
                    'order_type': random.randint(1, 5),
                    'title': random.choice([
                        f"{random.choice(INDUSTRIES)}行业CRM系统定制",
                        f"{random.choice(INDUSTRIES)}信息化管理系统",
                        "企业数字化转型方案",
                        "客户管理系统维护服务",
                        "业务咨询与培训服务",
                        "软件产品采购合同",
                        "系统集成项目",
                        "数据分析平台建设"
                    ]),
                    'description': "详细需求待进一步沟通确认",
                    'amount': random.randint(10000, 500000),
                    'status': random.choices(
                        population=[1, 2, 3, 4, 5],
                        weights=[10, 30, 40, 5, 15]
                    )[0],
                    'payment_status': random.randint(1, 4),
                    'contract_status': random.randint(1, 3),
                    'user_id': customer['user_id'],
                    'create_time': order_time,
                    'update_time': order_time
                }
                orders.append(order)
                order_id += 1

    return orders

def generate_sql(customers: List[Dict], contacts: List[Dict], followups: List[Dict], orders: List[Dict]) -> str:
    """生成SQL插入语句"""

    sql = "-- 营销CRM测试数据\n"
    sql += "-- 生成时间: " + datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') + "\n\n"

    # 插入客户数据
    sql += "-- 插入客户数据 (oa_crm_customer)\n"
    sql += "INSERT INTO `oa_crm_customer` ("
    sql += "`name`, `legal_person`, `phone`, `industry`, `scale`, `source`, "
    sql += "`province`, `city`, `district`, `address`, `type`, `status`, "
    sql += "`cooperation_time`, `user_id`, `department_id`, `create_time`, `update_time`"
    sql += ") VALUES\n"

    for i, customer in enumerate(customers):
        create_time = customer['create_time'].strftime('%Y-%m-%d %H:%M:%S')
        update_time = customer['update_time'].strftime('%Y-%m-%d %H:%M:%S') if customer['update_time'] else 'NULL'
        coop_time = str(customer['cooperation_time']) if customer['cooperation_time'] else 'NULL'

        sql += f"("
        sql += f"'{customer['name']}', "
        sql += f"'{customer['legal_person']}', "
        sql += f"'{customer['phone']}', "
        sql += f"'{customer['industry']}', "
        sql += f"'{customer['scale']}', "
        sql += f"{customer['source']}, "
        sql += f"'{customer['province']}', "
        sql += f"'{customer['city']}', "
        sql += f"'{customer['district']}', "
        sql += f"'{customer['address']}', "
        sql += f"{customer['type']}, "
        sql += f"{customer['status']}, "
        sql += f"{coop_time}, "
        sql += f"{customer['user_id']}, "
        sql += f"{customer['department_id']}, "
        sql += f"'{create_time}', "
        sql += f"'{update_time}'"
        sql += f")"

        if i < len(customers) - 1:
            sql += ","
        else:
            sql += ";"
        sql += "\n\n"

    # 插入联系人数据
    sql += "\n-- 插入联系人数据 (oa_crm_contact)\n"
    sql += "INSERT INTO `oa_crm_contact` ("
    sql += "`customer_id`, `name`, `position`, `phone`, `wechat`, `email`, "
    sql += "`is_primary`, `status`, `user_id`, `create_time`, `update_time`"
    sql += ") VALUES\n"

    for i, contact in enumerate(contacts):
        create_time = contact['create_time'].strftime('%Y-%m-%d %H:%M:%S')
        update_time = contact['update_time'].strftime('%Y-%m-%d %H:%M:%S') if contact['update_time'] else 'NULL'

        sql += f"("
        sql += f"{contact['customer_id']}, "
        sql += f"'{contact['name']}', "
        sql += f"'{contact['position']}', "
        sql += f"'{contact['phone']}', "
        sql += f"'{contact['wechat']}', "
        sql += f"'{contact['email']}', "
        sql += f"{contact['is_primary']}, "
        sql += f"{contact['status']}, "
        sql += f"{contact['user_id']}, "
        sql += f"'{create_time}', "
        sql += f"'{update_time}'"
        sql += f")"

        if i < len(contacts) - 1:
            sql += ","
        else:
            sql += ";"
        sql += "\n\n"

    # 插入跟进记录
    sql += "\n-- 插入跟进记录 (oa_crm_followup)\n"
    sql += "INSERT INTO `oa_crm_followup` ("
    sql += "`customer_id`, `followup_type`, `followup_time`, `content`, `followup_result`, "
    sql += "`next_followup_time`, `user_id`, `create_time`, `update_time`"
    sql += ") VALUES\n"

    for i, followup in enumerate(followups):
        create_time = followup['create_time'].strftime('%Y-%m-%d %H:%M:%S')
        update_time = followup['update_time'].strftime('%Y-%m-%d %H:%M:%S')

        sql += f"("
        sql += f"{followup['customer_id']}, "
        sql += f"{followup['followup_type']}, "
        sql += f"'{followup['followup_time']}', "
        sql += f"'{followup['content']}', "
        sql += f"{followup['followup_result']}, "
        sql += f"'{followup['next_followup_time']}', "
        sql += f"{followup['user_id']}, "
        sql += f"'{create_time}', "
        sql += f"'{update_time}'"
        sql += f")"

        if i < len(followups) - 1:
            sql += ","
        else:
            sql += ";"
        sql += "\n\n"

    # 插入订单数据
    if orders:
        sql += "\n-- 插入订单数据 (oa_crm_order)\n"
        sql += "INSERT INTO `oa_crm_order` ("
        sql += "`customer_id`, `order_type`, `title`, `description`, `amount`, "
        sql += "`status`, `payment_status`, `contract_status`, `user_id`, `create_time`, `update_time`"
        sql += ") VALUES\n"

        for i, order in enumerate(orders):
            create_time = order['create_time'].strftime('%Y-%m-%d %H:%M:%S')
            update_time = order['update_time'].strftime('%Y-%m-%d %H:%M:%S')

            sql += f"("
            sql += f"{order['customer_id']}, "
            sql += f"{order['order_type']}, "
            sql += f"'{order['title']}', "
            sql += f"'{order['description']}', "
            sql += f"{order['amount']}, "
            sql += f"{order['status']}, "
            sql += f"{order['payment_status']}, "
            sql += f"{order['contract_status']}, "
            sql += f"{order['user_id']}, "
            sql += f"'{create_time}', "
            sql += f"'{update_time}'"
            sql += f")"

            if i < len(orders) - 1:
                sql += ","
            else:
                sql += ";"
            sql += "\n\n"

    return sql

def generate_summary(customers: List[Dict], contacts: List[Dict], followups: List[Dict], orders: List[Dict]) -> str:
    """生成数据统计摘要"""
    summary = "\n" + "="*60 + "\n"
    summary += "营销CRM测试数据生成完成\n"
    summary += "="*60 + "\n\n"

    # 客户统计
    summary += f"客户总数: {len(customers)}\n"
    summary += "-" * 60 + "\n"
    status_count = {}
    for customer in customers:
        status = customer['status']
        status_count[status] = status_count.get(status, 0) + 1

    for status, count in sorted(status_count.items()):
        summary += f"  {CUSTOMER_STATUS[status]}: {count}个\n"

    # 联系人统计
    summary += f"\n联系人总数: {len(contacts)}\n"
    summary += f"平均每客户联系人数: {len(contacts)/len(customers):.1f}个\n"

    # 跟进记录统计
    summary += f"\n跟进记录总数: {len(followups)}\n"
    summary += f"平均每客户跟进次数: {len(followups)/len(customers):.1f}次\n"

    # 订单统计
    if orders:
        summary += f"\n订单总数: {len(orders)}\n"
        total_amount = sum(order['amount'] for order in orders)
        summary += f"订单总金额: ¥{total_amount:,}\n"
        summary += f"平均订单金额: ¥{total_amount/len(orders):,.0f}\n"

        order_status_count = {}
        for order in orders:
            status = order['status']
            order_status_count[status] = order_status_count.get(status, 0) + 1

        summary += "\n订单状态分布:\n"
        for status, count in sorted(order_status_count.items()):
            summary += f"  {ORDER_STATUS[status]}: {count}个\n"

    summary += "\n" + "="*60 + "\n"

    return summary

def main():
    """主函数"""
    print("正在生成营销CRM测试数据...")
    print(f"目标客户数量: {CUSTOMER_COUNT}\n")

    # 设置随机种子，保证可重现
    random.seed(42)

    # 生成数据
    customers = generate_customers(CUSTOMER_COUNT)
    contacts = generate_contacts(customers)
    followups = generate_followups(customers)
    orders = generate_orders(customers)

    # 生成SQL
    sql = generate_sql(customers, contacts, followups, orders)

    # 保存SQL文件
    output_file = "crm_test_data.sql"
    with open(output_file, 'w', encoding='utf-8') as f:
        f.write(sql)

    # 输出统计信息
    print(generate_summary(customers, contacts, followups, orders))
    print(f"SQL文件已生成: {output_file}")
    print("\n使用方法:")
    print("1. 备份数据库（重要！）")
    print(f"2. 执行SQL: mysql -u用户名 -p密码 数据库名 < {output_file}")

if __name__ == "__main__":
    main()
